Go to the documentation of this file.
00001 
00028 using System;
00029 using System.Collections.Generic;
00030 using System.Data.SqlClient;
00031 using System.Text;
00032 using System.Xml;
00033 
00034 using RAIS.Common.DynamicMaskManagement;
00035 using RAIS.Common.UserManagement;
00036 
00037 using DataRoles = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.DataRole>;
00038 using DataRoleAttributes = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.DataRoleAttribute>;
00039 using FunctionRoles = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.FunctionRole>;
00040 using FunctionRoleAttributes = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.FunctionRoleAttribute>;
00041 using FunctionRoleValidationTypes = System.Collections.Generic.Dictionary<RAIS.Common.UserManagement.FunctionRole.ValidationType, RAIS.Common.UserManagement.FunctionRole.ValidationTypeData>;
00042 using FunctionRoleTypes = System.Collections.Generic.Dictionary<RAIS.Common.UserManagement.FunctionRole.FunctionRoleType, RAIS.Common.UserManagement.FunctionRole.FunctionRoleTypeData>;
00043 using UserTypes = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.UserTypeData>;
00044 using UserStatuses = System.Collections.Generic.Dictionary<RAIS.Common.UserManagement.UserData.UserStatus, RAIS.Common.UserManagement.UserStatusData>;
00045 using Users = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.UserData>;
00046 using FunctionRoleCategories = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.FunctionRole.Category>;
00047 using FunctionRoleSubcategories = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.FunctionRole.Subcategory>;
00048 using Masks = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.Mask>;
00049 using Regions = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.Region>;
00050 using Districts = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.District>;
00051 using Facilities = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.Facility>;
00052 using Practices = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.Practice>;
00053 using FunctionRoleValidationTypesByName = System.Collections.Generic.Dictionary<string, RAIS.Common.UserManagement.FunctionRole.ValidationType>;
00054 using MessageBoxRestrictions = System.Collections.Generic.List<RAIS.Common.UserManagement.MessageBoxRestriction>;
00055 
00056 namespace RAIS.DataAccessLayer
00057 {
00061     public class UserManagement
00062     {
00063         #region Static fields
00064         //static string[] userTypes; ///< User types list
00065                 #endregion
00066 
00067         #region Constructor
00068         static UserManagement()
00069         {
00070                         UserData.UserTypes = GetUserTypes();
00071                         UserData.UserStatuses = GetUserStatuses();
00072                         FunctionRole.ValidationTypesDictionary = GetFunctionRoleValidationTypes();
00073             FunctionRole.ValidationTypeNamesDictionary = new FunctionRoleValidationTypesByName();
00074                         FunctionRole.FunctionRoleTypesDictionary = GetFunctionRoleTypes();
00075 
00076             foreach (KeyValuePair<FunctionRole.ValidationType, FunctionRole.ValidationTypeData> kvp in FunctionRole.ValidationTypesDictionary)
00077             {
00078                 FunctionRole.ValidationTypeNamesDictionary.Add(kvp.Value.VisibleName, kvp.Key);
00079             }
00080         }
00081         #endregion
00082 
00083                 #region Properties
00084 
00092                 public static UserTypes UserTypes
00093                 {
00094                         get
00095                         {
00096                                 return UserData.UserTypes;
00097                         }
00098                 }
00106                 public static Users Users
00107                 {
00108                         get
00109                         {
00110                                 Users retValue = GetUsers();
00111                                 return retValue;
00112                         }
00113                 }
00121                 public static Users Validators
00122                 {
00123                         get
00124                         {
00125                                 Users retValue = GetValidators();
00126                                 return retValue;
00127                         }
00128                 }
00136                 public static FunctionRoles FunctionalRoles
00137                 {
00138                         get
00139                         {
00140                                 return GetFunctionalRoles(false);
00141                         }
00142                 }
00150                 public static FunctionRoles PredefinedFunctionalRoles
00151                 {
00152                         get
00153                         {
00154                                 return GetFunctionalRoles(true);
00155                         }
00156                 }
00164                 public static FunctionRoleCategories FunctionalRoleCategories
00165                 {
00166                         get
00167                         {
00168                                 return GetFunctionalRoleCategories();
00169                         }
00170                 }
00178                 public static DataRoles DataRoles
00179                 {
00180                         get
00181                         {
00182                                 DataRoles retValue = GetDataRoles();
00183                                 return retValue;
00184                         }
00185                 }
00193                 public static Regions Regions
00194                 {
00195                         get
00196                         {
00197                                 Regions retValue = GetRegions();
00198                                 return retValue;
00199                         }
00200                 }
00208                 public static Facilities Facilities
00209                 {
00210                         get
00211                         {
00212                                 Facilities retValue = GetFacilities();
00213                                 return retValue;
00214                         }
00215                 }
00224         public static Practices Practices
00225         {
00226             get
00227             {
00228                 Practices retValue = GetPractices();
00229                 return retValue;
00230             }
00231         }
00232 
00233                 #endregion
00234 
00235                 #region Public static methods
00236 
00245         public static UserData GetUserData(string userName)
00246         {
00247                         UserData user = GetUserDataByName(userName);
00248 
00249                         if (user == null)
00250                 throw new Exception("User " + userName + " not found");
00251 
00252             return user;
00253         }
00254 
00255         //#-#
00256         public static List<Facility> getFacilitiesByPractice(int practiceID)
00257         {
00258             List<Facility> facilities = new List<Facility>();
00259             using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00260             {
00261                 sqlConnection.Open();
00262 
00263                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFacilitiesByPractice, sqlConnection))
00264                 {
00265                     sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00266                     DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_PracticeID, practiceID);
00267 
00268                     using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
00269                     {
00270                         while (sqlReader.Read())
00271                         {
00272                             Facility f = new Facility(sqlReader);
00273                             facilities.Add(f);
00274                         }
00275                     }
00276                 }
00277             }
00278             return facilities;
00279         }
00280 
00281         //#-#
00282         public static List<UserAssignment> GetUserAssignmentsForUser(UserData user, DateTime selectedDate)
00283         {
00284             List<UserAssignment> uas = new List<UserAssignment>();
00285             using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00286             {
00287                 sqlConnection.Open();
00288 
00289                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetUserAssignmentsForUser, sqlConnection))
00290                 {
00291                     sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00292                     DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, user.ID);
00293                     DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SelectedDate, selectedDate);
00294 
00295                     using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
00296                     {
00297                         while (sqlReader.Read())
00298                         {
00299                             UserAssignment ua = new UserAssignment(sqlReader);
00300                             uas.Add(ua);
00301                         }
00302                     }
00303                 }
00304             }
00305             return uas;
00306         }
00307         public static List<UserAssignment> GetUserAssignmentsForUser(UserData user)
00308         {
00309             List<UserAssignment> uas = new List<UserAssignment>();
00310             using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00311             {
00312                 sqlConnection.Open();
00313 
00314                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetUserAssignmentsForUser, sqlConnection))
00315                 {
00316                     sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00317                     DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, user.ID);
00318                     DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SelectedDate, null);
00319 
00320                     using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
00321                     {
00322                         while (sqlReader.Read())
00323                         {
00324                             UserAssignment ua = new UserAssignment(sqlReader);
00325                             uas.Add(ua);
00326                         }
00327                     }
00328                 }
00329             }
00330             return uas;
00331         }
00332 
00333         //#-#
00334         public static List<UserAssignment> GetUserAssignmentHistoryForUser(UserData user)
00335         {
00336             List<UserAssignment> history = new List<UserAssignment>();
00337             using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00338             {
00339                 sqlConnection.Open();
00340 
00341                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetUserAssignmentHistoryForUser, sqlConnection))
00342                 {
00343                     sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00344                     DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, user.ID);
00345 
00346                     using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
00347                     {
00348                         while (sqlReader.Read())
00349                         {
00350                             UserAssignment ua = new UserAssignment(sqlReader);
00351                             history.Add(ua);
00352                         }
00353                     }
00354                 }
00355             }
00356             return history;
00357         }
00358 
00366         public static bool UserExists(string userName)
00367         {
00368             return null != GetUserDataByName(userName);
00369         }
00376                 public static bool LoginUser(UserData userData)
00377                 {
00378                         try
00379                         {
00380                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00381                                 {
00382                                         sqlConnection.Open();
00383 
00384                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_LoginUser, sqlConnection))
00385                                         {
00386                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00387                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, userData.ID);
00388                                                 sqlCommand.ExecuteNonQuery();
00389                                         }
00390                                 }
00391                                 return true;
00392                         }
00393                         catch
00394                         {
00395                                 return false;
00396                         }
00397                 }
00404                 public static bool LogoutUser(UserData userData, bool isLoggOffClick)
00405                 {
00406                         try
00407                         {
00408                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00409                                 {
00410                                         sqlConnection.Open();
00411 
00412                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_LogoutUser, sqlConnection))
00413                                         {
00414                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00415                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, userData.ID);
00416                         if (isLoggOffClick)
00417                             DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_IsLoggOffClick, 1);
00418                                                 sqlCommand.ExecuteNonQuery();
00419                                         }
00420                                 }
00421                                 return true;
00422                         }
00423                         catch
00424                         {
00425                                 return false;
00426                         }
00427                 }
00434                 public static bool AddAccountRequest(UserData userData, ref string returnMessage)
00435                 {
00436                         try
00437                         {
00438                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00439                                 {
00440                                         sqlConnection.Open();
00441 
00442                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddAccountRequest, sqlConnection))
00443                                         {
00444                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00445                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserName, userData.Login);
00446                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Password, userData.Password);
00447                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CompleteName, userData.CompleteName);
00448                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Email, userData.Email);
00449                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, userData.Type.ID);
00450                                                 if (userData.FunctionalRole != null)
00451                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, userData.FunctionalRole.ID);
00452                                                 else
00453                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, null);
00454                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserStatusID, userData.StatusData.ID);
00455                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine1, userData.AddressLine1);
00456                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine2, userData.AddressLine2);
00457                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine3, userData.AddressLine3);
00458                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_City, userData.City);
00459                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_PostCode, userData.PostCode);
00460                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Comment, userData.AccountRequestComment);
00461                                                 sqlCommand.ExecuteNonQuery();
00462                                         }
00463                                 }
00464                                 returnMessage = RAIS.Common.Messages.ACCOUNT_REQUEST_SAVED_MESSAGE;
00465                                 return true;
00466                         }
00467                         catch (Exception excep)
00468                         {
00469                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00470                                 return false;
00471                         }
00472                 }
00479                 public static bool ReleaseAccountRequest(UserData userData, ref string returnMessage)
00480                 {
00481                         try
00482                         {
00483                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00484                                 {
00485                                         sqlConnection.Open();
00486 
00487                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_ReleaseAccountRequest, sqlConnection))
00488                                         {
00489                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00490                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MessageID, userData.AccountRequest.ID);
00491                                                 sqlCommand.ExecuteNonQuery();
00492                                         }
00493                                 }
00494                                 returnMessage = RAIS.Common.Messages.ACCOUNT_REQUEST_SAVED_MESSAGE;
00495                                 return true;
00496                         }
00497                         catch (Exception excep)
00498                         {
00499                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00500                                 return false;
00501                         }
00502                 }
00509                 public static bool RejectAccountRequest(UserData userData, ref string returnMessage)
00510                 {
00511                         try
00512                         {
00513                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00514                                 {
00515                                         sqlConnection.Open();
00516 
00517                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_RejectAccountRequest, sqlConnection))
00518                                         {
00519                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00520                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MessageID, userData.AccountRequest.ID);
00521                                                 sqlCommand.ExecuteNonQuery();
00522                                         }
00523                                 }
00524                                 returnMessage = RAIS.Common.Messages.ACCOUNT_REQUEST_SAVED_MESSAGE;
00525                                 return true;
00526                         }
00527                         catch (Exception excep)
00528                         {
00529                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00530                                 return false;
00531                         }
00532                 }
00539         public static bool AddUser(UserData userData, ref string returnMessage)
00540         {
00541             try
00542             {
00543                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00544                 {
00545                     sqlConnection.Open();
00546 
00547                     using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddUser, sqlConnection))
00548                     {
00549                         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00550                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, userData.ID, System.Data.ParameterDirection.Output);
00551                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserName, userData.Login);
00552                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Password, userData.Password);
00553                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CompleteName, userData.CompleteName);
00554                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Email, userData.Email);
00555                                                 if (userData.Type != null)
00556                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, userData.Type.ID);
00557                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserStatusID, userData.StatusData.ID);
00558                                                 if (userData.DataRoleSpecified && userData.DataRole != null)
00559                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, userData.DataRole.ID);
00560                                                 if (userData.FunctionalRole != null)
00561                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, userData.FunctionalRole.ID);
00562                                                 if (userData.FacilitySpecified && userData.Facility != null)
00563                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FacilityID, userData.Facility.ID);
00564                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine1, userData.AddressLine1);
00565                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine2, userData.AddressLine2);
00566                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine3, userData.AddressLine3);
00567                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_City, userData.City);
00568                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_PostCode, userData.PostCode);
00569                                                 sqlCommand.ExecuteNonQuery();
00570 
00571                         userData.ID = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_UserID].Value.ToString());
00572                     }
00573                 }
00574                 return true;
00575             }
00576             catch(Exception excep)
00577             {
00578                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00579                 return false;
00580             }
00581                 }
00588         public static bool ModifyUser(UserData userData, ref string returnMessage)
00589         {
00590             try
00591             {
00592                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00593                 {
00594                     sqlConnection.Open();
00595 
00596                     using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateUser, sqlConnection))
00597                     {
00598                         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00599                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserID, userData.ID);
00600                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserName, userData.Login);
00601                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Password, userData.Password);
00602                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CompleteName, userData.CompleteName);
00603                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Email, userData.Email);
00604                                                 if (userData.Type != null)
00605                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, userData.Type.ID);
00606                                                 else
00607                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, null);
00608                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserStatusID, userData.StatusData.ID);
00609                                                 if (userData.DataRole != null)
00610                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, userData.DataRole.ID);
00611                                                 else
00612                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, null);
00613                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, userData.FunctionalRole.ID);
00614                                                 if (userData.Facility != null)
00615                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FacilityID, userData.Facility.ID);
00616                                                 else
00617                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FacilityID, null);
00618                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine1, userData.AddressLine1);
00619                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine2, userData.AddressLine2);
00620                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddressLine3, userData.AddressLine3);
00621                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_City, userData.City);
00622                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_PostCode, userData.PostCode);
00623                                                 sqlCommand.ExecuteNonQuery();
00624                     }
00625                 }
00626                 return true;
00627             }
00628             catch (Exception excep)
00629             {
00630                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00631                 return false;
00632             }
00633         }
00640         public static bool AddDataRole(string dataRoleName, ref string returnMessage)
00641         {
00642             try
00643             {
00644                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00645                                 {
00646                                         sqlConnection.Open();
00647 
00648                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddDataRole, sqlConnection))
00649                                         {
00650                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00651                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleName, dataRoleName);
00652                                                 sqlCommand.ExecuteNonQuery();
00653                                         }
00654                                 }
00655                                 returnMessage = RAIS.Common.Messages.DATA_ROLE_ADDED;
00656                 return true;
00657             }
00658             catch(Exception excep)
00659             {
00660                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00661                 return false;
00662             }
00663                 }
00671                 public static bool SaveDataRole(DataRole dataRole, ref string returnMessage)
00672                 {
00673                         try
00674                         {
00675                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00676                                 {
00677                                         sqlConnection.Open();
00678 
00679                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateDataRole, sqlConnection))
00680                                         {
00681                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00682                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, dataRole.ID);
00683                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleName, dataRole.Name);
00684                                                 sqlCommand.ExecuteNonQuery();
00685                                         }
00686                                 }
00687                                 returnMessage = RAIS.Common.Messages.DATA_ROLE_SAVED;
00688                                 return true;
00689                         }
00690                         catch (Exception excep)
00691                         {
00692                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00693                                 return false;
00694                         }
00695                 }
00703                 public static bool RemoveDataRole(DataRole dataRole, ref string returnMessage)
00704                 {
00705                         try
00706                         {
00707                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00708                                 {
00709                                         sqlConnection.Open();
00710 
00711                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteDataRole, sqlConnection))
00712                                         {
00713                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00714                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, dataRole.ID);
00715                                                 sqlCommand.ExecuteNonQuery();
00716                                         }
00717                                 }
00718 
00719                                 returnMessage = RAIS.Common.Messages.DATA_ROLE_REMOVED;
00720                                 return true;
00721                         }
00722                         catch (Exception excep)
00723                         {
00724                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00725                                 return false;
00726                         }
00727                 }
00735         public static bool AddDataRoleAttribute(DataRole dataRole, DataRoleAttribute dataRoleAttribute, ref string returnMessage)
00736         {
00737             try
00738             {
00739                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00740                                 {
00741                                         sqlConnection.Open();
00742 
00743                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddDataRoleRestriction, sqlConnection))
00744                                         {
00745                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00746                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, dataRole.ID);
00747                                                 if (dataRoleAttribute.Region.ID == 0)
00748                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, null);
00749                                                 else
00750                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, dataRoleAttribute.Region.ID);
00751                                                 if (dataRoleAttribute.District.ID == 0)
00752                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DistrictID, null);
00753                                                 else
00754                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DistrictID, dataRoleAttribute.District.ID);
00755                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FacilityID, dataRoleAttribute.Facility.ID);
00756                                                 sqlCommand.ExecuteNonQuery();
00757                                         }
00758                                 }
00759                 dataRole.Attributes[dataRoleAttribute.ToString()] = dataRoleAttribute;
00760                 returnMessage = RAIS.Common.Messages.DATA_ROLE_ATTRIBUTE_ADDED;
00761                 return true;
00762             }
00763             catch (Exception excep)
00764             {
00765                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00766                 return false;
00767             }
00768         }
00777         public static bool RemoveDataRoleAttribute(DataRole dataRole, DataRoleAttribute dataRoleAttribute, ref string returnMessage)
00778         {
00779             try
00780             {
00781                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00782                                 {
00783                                         sqlConnection.Open();
00784 
00785                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteDataRoleRestriction, sqlConnection))
00786                                         {
00787                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00788                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DataRoleID, dataRole.ID);
00789                                                 if (dataRoleAttribute.Region.ID == 0)
00790                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, null);
00791                                                 else
00792                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, dataRoleAttribute.Region.ID);
00793                                                 if (dataRoleAttribute.District.ID == 0)
00794                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DistrictID, null);
00795                                                 else
00796                                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DistrictID, dataRoleAttribute.District.ID);
00797                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FacilityID, dataRoleAttribute.Facility.ID);
00798                                                 sqlCommand.ExecuteNonQuery();
00799                                         }
00800                                 }
00801                 dataRole.Attributes.Remove(dataRoleAttribute.ToString());
00802                 returnMessage = RAIS.Common.Messages.DATA_ROLE_ATTRIBUTE_REMOVED;
00803                 return true;
00804             }
00805             catch (Exception excep)
00806             {
00807                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00808                 return false;
00809             }
00810         }
00818         public static bool AddFunctionRole(string functionRoleName, ref string returnMessage)
00819         {
00820             return AddFunctionRole(functionRoleName, FunctionRole.DefaltValidation, ref returnMessage);
00821         }
00830         public static bool AddFunctionRole(string functionRoleName, FunctionRole.ValidationType validationType, ref string returnMessage)
00831         {
00832             try
00833             {
00834                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00835                 {
00836                     sqlConnection.Open();
00837 
00838                     using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddFunctionalRole, sqlConnection))
00839                     {
00840                         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00841                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleName, functionRoleName);
00842                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleValidationTypeID, FunctionRole.ValidationTypesDictionary[validationType].ID);
00843                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleTypeID, FunctionRole.FunctionRoleTypesDictionary[FunctionRole.DefaltType].ID);
00844                         sqlCommand.ExecuteNonQuery();
00845                     }
00846                 }
00847                 returnMessage = RAIS.Common.Messages.FUNCTION_ROLE_ADDED;
00848                 return true;
00849             }
00850             catch (Exception excep)
00851             {
00852                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00853                 return false;
00854             }
00855         }
00863                 public static bool SaveFunctionRole(FunctionRole functionRole, ref string returnMessage)
00864                 {
00865                         try
00866                         {
00867                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00868                                 {
00869                                         sqlConnection.Open();
00870 
00871                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_UpdateFunctionalRole, sqlConnection))
00872                                         {
00873                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00874                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
00875                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleName, functionRole.Name);
00876                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleValidationTypeID, functionRole.ValidationData.ID);
00877                                                 sqlCommand.ExecuteNonQuery();
00878                                         }
00879                                 }
00880                                 returnMessage = RAIS.Common.Messages.FUNCTION_ROLE_SAVED;
00881                                 return true;
00882                         }
00883                         catch (Exception excep)
00884                         {
00885                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00886                                 return false;
00887                         }
00888                 }
00896                 public static bool RemoveFunctionRole(FunctionRole functionRole, ref string returnMessage)
00897                 {
00898                         try
00899                         {
00900                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00901                                 {
00902                                         sqlConnection.Open();
00903 
00904                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteFunctionalRole, sqlConnection))
00905                                         {
00906                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00907                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
00908                                                 sqlCommand.ExecuteNonQuery();
00909                                         }
00910                                 }
00911                                 returnMessage = RAIS.Common.Messages.FUNCTION_ROLE_REMOVED;
00912                                 return true;
00913                         }
00914                         catch (Exception excep)
00915                         {
00916                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00917                                 return false;
00918                         }
00919                 }
00926                 public static bool AddFunctionRoleAttribute(FunctionRole functionRole, FunctionRoleAttribute functionRoleAttribute, ref string returnMessage)
00927                 {
00928                         try
00929                         {
00930                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00931                                 {
00932                                         sqlConnection.Open();
00933 
00934                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddFunctionalRoleRestriction, sqlConnection))
00935                                         {
00936                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00937                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
00938                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_ViewAccess, functionRoleAttribute.AccessView);
00939                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_AddAccess, functionRoleAttribute.AccessAdd);
00940                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_EditAccess, functionRoleAttribute.AccessEdit);
00941                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DeleteAccess, functionRoleAttribute.AccessDelete);
00942                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CategoryID, functionRoleAttribute.Category.ID);
00943                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SubcategoryID, functionRoleAttribute.Subcategory.ID);
00944                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MaskID, functionRoleAttribute.Mask.ID);
00945                                                 sqlCommand.ExecuteNonQuery();
00946                                         }
00947                                 }
00948                 functionRole.Attributes[functionRoleAttribute.ToString()] = functionRoleAttribute;
00949                 returnMessage = RAIS.Common.Messages.FUNCTION_ROLE_ATTRIBUTE_ADDED;
00950                                 return true;
00951                         }
00952                         catch (Exception excep)
00953                         {
00954                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00955                                 return false;
00956                         }
00957                 }
00966                 public static bool RemoveFunctionRoleAttribute(FunctionRole functionRole, FunctionRoleAttribute functionRoleAttribute, ref string returnMessage)
00967                 {
00968                         try
00969                         {
00970                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
00971                                 {
00972                                         sqlConnection.Open();
00973 
00974                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteFunctionalRoleRestriction, sqlConnection))
00975                                         {
00976                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
00977                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
00978                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_CategoryID, functionRoleAttribute.Category.ID);
00979                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_SubcategoryID, functionRoleAttribute.Subcategory.ID);
00980                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_MaskID, functionRoleAttribute.Mask.ID);
00981                                                 sqlCommand.ExecuteNonQuery();
00982                                         }
00983                                 }
00984                                 functionRole.Attributes.Remove(functionRoleAttribute.ToString());
00985                                 returnMessage = RAIS.Common.Messages.FUNCTION_ROLE_ATTRIBUTE_REMOVED;
00986                                 return true;
00987                         }
00988                         catch (Exception excep)
00989                         {
00990                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
00991                                 return false;
00992                         }
00993                 }
01002                 public static bool AddUserType(FunctionRole functionRole, UserTypeData userType, ref string returnMessage)
01003                 {
01004                         try
01005                         {
01006                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
01007                                 {
01008                                         sqlConnection.Open();
01009 
01010                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_AddUserTypeFunctionalRole, sqlConnection))
01011                                         {
01012                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
01013                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
01014                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, userType.ID);
01015                                                 sqlCommand.ExecuteNonQuery();
01016                                         }
01017                                 }
01018                                 returnMessage = RAIS.Common.Messages.USER_TYPE_ADDED;
01019                                 return true;
01020                         }
01021                         catch (Exception excep)
01022                         {
01023                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
01024                                 return false;
01025                         }
01026                 }
01035                 public static bool RemoveUserType(FunctionRole functionRole, UserTypeData userType, ref string returnMessage)
01036                 {
01037                         try
01038                         {
01039                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
01040                                 {
01041                                         sqlConnection.Open();
01042 
01043                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_DeleteUserTypeFunctionalRole, sqlConnection))
01044                                         {
01045                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
01046                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
01047                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, userType.ID);
01048                                                 sqlCommand.ExecuteNonQuery();
01049                                         }
01050                                 }
01051                                 returnMessage = RAIS.Common.Messages.USER_TYPE_REMOVED;
01052                                 return true;
01053                         }
01054                         catch (Exception excep)
01055                         {
01056                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
01057                                 return false;
01058                         }
01059                 }
01060 
01061                 public static bool ValidateRemoveUserTypeFromFunctionRole(FunctionRole functionRole, UserTypeData userType, ref string returnMessage)
01062                 {
01063                         try
01064                         {
01065                                 bool success = true;
01066 
01067                                 using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
01068                                 {
01069                                         sqlConnection.Open();
01070 
01071                                         using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_ValidateDeleteUserTypeFunctionalRole, sqlConnection))
01072                                         {
01073                                                 sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
01074                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_FunctionalRoleID, functionRole.ID);
01075                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_UserTypeID, userType.ID);
01076                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_Success, success, System.Data.ParameterDirection.Output);
01077                                                 sqlCommand.ExecuteNonQuery();
01078 
01079                                                 int validationResult = int.Parse(sqlCommand.Parameters[Constants.SP_PARAMETER_Success].Value.ToString());
01080 
01081                                                 if (validationResult == 0)
01082                                                 {
01083                                                         returnMessage = RAIS.Common.Messages.USER_TYPE_CAN_NOT_BE_REMOVED;
01084                                                         success = false;
01085                                                 }
01086                                         }
01087                                 }
01088                                 return success;
01089                         }
01090                         catch (Exception excep)
01091                         {
01092                 returnMessage = excep.Message + Constants.DATA_BASE_ERROR_MASSAGE;
01093                                 return false;
01094                         }
01095                 }
01101         public static Districts GetDistrictsByRegion(Region region)
01102         {
01103                         Districts districts = new Districts();
01104 
01105                         using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
01106                         {
01107                                 sqlConnection.Open();
01108 
01109                                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetDistrictsByRegion, sqlConnection))
01110                                 {
01111                                         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
01112                                         DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, region.ID);
01113 
01114                                         using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
01115                                         {
01116                                                 while (sqlReader.Read())
01117                                                 {
01118                                                         District district = new District(sqlReader);
01119                                                         districts.Add(district.Name, district);
01120                                                 }
01121                                         }
01122                                 }
01123                         }
01124             return districts;
01125         }
01132         public static Facilities GetFacilitiesByRegionAndDistrict(Region region, District district)
01133         {
01134                         Facilities facilities = new Facilities();
01135 
01136                         using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
01137                         {
01138                                 sqlConnection.Open();
01139 
01140                                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetFacilitiesByRegionIdAndDistrictId, sqlConnection))
01141                                 {
01142                                         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
01143                                         if (region.ID == 0)
01144                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, null);
01145                                         else
01146                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_RegionID, region.ID);
01147                                         
01148                                         if (district.ID == 0)
01149                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DistrictID, null);
01150                                         else
01151                                                 DataAccessUtilities.AddParameter(sqlCommand, Constants.SP_PARAMETER_DistrictID, district.ID);
01152 
01153                                         using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
01154                                         {
01155                                                 while (sqlReader.Read())
01156                                                 {
01157                                                         Facility facility = new Facility(sqlReader);
01158                                                         facilities.Add(facility.Name, facility);
01159                                                 }
01160                                         }
01161                                 }
01162                         }
01163 
01164                         return facilities;
01165         }
01171                 public static FunctionRoleCategories GetAllowCustomizationCategories()
01172                 {
01173                         FunctionRoleCategories functionalRoleCategories =
01174                                 new FunctionRoleCategories();
01175 
01176                         using (SqlConnection sqlConnection = new SqlConnection(DataAccessUtilities.ConnectionString))
01177                         {
01178                                 sqlConnection.Open();
01179 
01180                                 using (SqlCommand sqlCommand = new SqlCommand(Constants.SP_NAME_GetAllowCustomizationCategories, sqlConnection))
01181                                 {
01182                                         sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
01183 
01184                                         using (SqlDataReader sqlReader = sqlCommand.ExecuteReader())
01185                                         {
01186                                                 while (sqlReader.Read())
01187                                                 {
01188                                                         FunctionRole.Category category = new FunctionRole.Category(sqlReader);
01189                                                         functionalRoleCategories.Add(category.Name, category);
01190                                                 }
01191                                         }
01192                                 }
01193                         }
01194                         return functionalRoleCategories;
01195                 }
01202 
01212 
01223 
01265 
01298 
01340 
01375 
01436 
01467 
01499 
01532 
01568 
01598 
01630 
01663 
01730 
01782 
01812 
01842 
01873 
01904 
01937 
01968 
01999 
02030 
02060 
02108 
02157 
02206 
02255 
02285 
02347 
02379 
02437 
02472 
02552 
02612 
02648 
02661 
02876 
02924